"""
基于 database.DatabaseManager 的 base_region_address 初始化工具
一键完成：省(level=2) → 市(level=3) → 区县(level=4) 的树形灌入
*/
"""
from __future__ import annotations

import os
from typing import List, Dict, Optional

from common.database import DatabaseManager
from common.logger import create_logger

# import requests

logger = create_logger("region_init", "INFO")

# 如需高德补坐标，设环境变量 AMAP_KEY
AMAP_KEY = os.getenv("AMAP_KEY")


class BaseRegionAddressInitTool:
	def __init__(self, db_manager: DatabaseManager):
		self.db = db_manager

	# ---------- 对外唯一入口 ----------
	def run(self, fill_coord: bool = True) -> None:
		logger.info("开始初始化 base_region_address ...")
		self._ensure_root()  # 中国节点
		provinces = self._pull_province()
		for p in provinces:
			p_id = self._insert_one(level=2, code=p["code"], name=p["name"], parent_id=1)
			cities = self._pull_city(p["code"])
			for c in cities:
				c_id = self._insert_one(level=3, code=c["code"], name=c["name"], parent_id=p_id)
				areas = self._pull_area(c["code"])
				for a in areas:
					self._insert_one(level=4, code=a["code"], name=a["name"], parent_id=c_id)
		if fill_coord:
			self._fill_coord_by_gaode()
		logger.info("base_region_address 初始化完成")

	# ---------- 拉取原始数据 ----------
	def _pull_province(self) -> List[Dict[str, str]]:
		sql = "SELECT code, name FROM base_region_province"
		return self.db.execute_query(sql)

	def _pull_city(self, province_code: str) -> List[Dict[str, str]]:
		sql = "SELECT code, name FROM base_region_city WHERE province_code = %s"
		return self.db.execute_query(sql, (province_code,))

	def _pull_area(self, city_code: str) -> List[Dict[str, str]]:
		sql = "SELECT code, name FROM base_region_area WHERE city_code = %s"
		return self.db.execute_query(sql, (city_code,))

	# ---------- 插入 & 幂等 ----------
	def _insert_one(self, level: int, code: str, name: str, parent_id: int) -> int:
		exist = self.db.execute_query(
			"SELECT id FROM base_region_address WHERE region_code = %s AND level = %s",
			(code, level)
		)
		if exist:
			return exist[0]["id"]
		full_name = self._build_full_name(parent_id, name)
		sql = """
        INSERT INTO base_region_address(parent_id, name, full_name, level, region_code, longitude, latitude)
        VALUES (%s, %s, %s, %s, %s, 0, 0)
        """
		self.db.execute_query(sql, (parent_id, name, full_name, level, code), commit=True)
		return self.db.last_insert_id()

	# ---------- 辅助 ----------
	def _ensure_root(self) -> None:
		has = self.db.execute_query("SELECT id FROM base_region_address WHERE level = 1")
		if not has:
			self.db.execute_query(
				"INSERT INTO base_region_address(id, name, full_name, level, region_code, longitude, latitude)"
				"VALUES (1, '中国', '中国', 1, '000000', 116.407526, 39.90403)",
				commit=True
			)

	def _build_full_name(self, parent_id: int, name: str) -> str:
		row = self.db.execute_query("SELECT full_name FROM base_region_address WHERE id = %s", (parent_id,))
		return f"{row[0]['full_name']},{name}" if row else name

	# ---------- 高德补坐标 ----------
	def _fill_coord_by_gaode(self) -> None:
		if not AMAP_KEY:
			logger.warning("AMAP_KEY 未设置，跳过坐标补录")
			return
		rows = self.db.execute_query(
			"SELECT id, region_code FROM base_region_address WHERE level IN (2,3,4) AND longitude = 0"
		)
		for r in rows:
			lon, lat = self._gaode_center(r["region_code"])
			if lon is not None:
				self.db.execute_query(
					"UPDATE base_region_address SET longitude = %s, latitude = %s WHERE id = %s",
					(lon, lat, r["id"]),
					commit=True
				)

	def _gaode_center(self, adcode: str) -> tuple[Optional[float], Optional[float]]:
		# url = f"https://restapi.amap.com/v3/config/district?key={AMAP_KEY}&keywords={adcode}&subdistrict=0"
		# try:
		# 	j = requests.get(url, timeout=3).json()
		# 	center = j["districts"][0]["center"].split(",")
		# 	return float(center[0]), float(center[1])
		# except Exception as e:
		# 	logger.debug("高德坐标获取失败 %s: %s", adcode, e)
		return None, None


if __name__ == "__main__":
	mgr = DatabaseManager.from_args(
		host=os.getenv("DB_HOST", "39.101.74.188"),
		port=int(os.getenv("DB_PORT", "30737")),
		user=os.getenv("DB_USER", "dba"),
		password=os.getenv("MYSQL_PWD", "DbaPass!"),
		database=os.getenv("DB_NAME", "materops"),
	)
	BaseRegionAddressInitTool(mgr).run(fill_coord=False)
